package com.jeecms.edu.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.HashedMap;

import com.jeecms.edu.utils.JDBCUtil;
import com.mysql.jdbc.ResultSetMetaData;


public class SchoolInformation {
	private static Connection conn;  
    private static PreparedStatement st; 
    private static ResultSet rs;
    private static Map<String,String> map;
    private static List<Object> list;
    
    public static String getSystime(){
    	String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
    	return time;
    } 
    
    
    //查询对应学校所在区域---上海市行政分区
    public static List checkSchoolRegion(String region,int page){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where region=? order by id limit ?,3";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1, region);
            st.setInt(2, page);
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("schoolregion", rs.getString("region").trim());
            	map.put("schooladdress", rs.getString("address").trim());
            	map.put("schoollength", rs.getString("schoollength").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("schooltel", rs.getString("schooltel").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("otherschoolbus", rs.getString("otherschoolbus").trim());
            	map.put("otherschoolbed", rs.getString("otherschoolbed").trim());
            	map.put("otherschoolkey", rs.getString("otherschoolkey").trim());
                list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("查询对应学校所在区域失败："+e.getMessage());
            return null;
        }
    }
    
    //查询对应民办学校---schoolnature
    public static List globalHotSchoolList(){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where schoolnature='民办' ORDER BY id";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("region", rs.getString("region").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
                list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("查询对应民办学校："+e.getMessage());
            return null;
        }
    }
    
    //查询对应非国际热门学校---schoolnature
    public static List localHotSchoolList(){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where schoolnature not in('国际') ORDER BY id";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("region", rs.getString("region").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("查询对应非国际热门学校："+e.getMessage());
            return null;
        }
    }
    
    //查询对应所有热门学校---schoolnature
    public static List AllHotSchoolList(){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school ORDER BY id";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("region", rs.getString("region").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
                list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("查询对应所有热门学校："+e.getMessage());
            return null;
        }
    }
    
    //查询对应学校类型级别---幼儿园、小学、初中、高中
    public static List checkSchoolType(String schooltype){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where schoollength in "+schooltype+" order by id";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
                list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("查询学校类型级别失败："+e.getMessage());
            return null;
        }
    }
    
    //查询对应学校类型级别---幼儿园、小学、初中、高中 带分页
    public static List checkSchoolTypeAll(String schooltype){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where schoollength in "+schooltype+" order by id";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("schoolregion", rs.getString("region").trim());
            	map.put("schooladdress", rs.getString("address").trim());
            	map.put("schoollength", rs.getString("schoollength").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("schooltel", rs.getString("schooltel").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("otherschoolbus", rs.getString("otherschoolbus").trim());
            	map.put("otherschoolbed", rs.getString("otherschoolbed").trim());
            	map.put("otherschoolkey", rs.getString("otherschoolkey").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
                list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("查询学校类型级别失败："+e.getMessage());
            return null;
        }
    }
    
  //查询对应学校类型级别---全部、示范园、民办、双语
    public static List checkSchoolLevel(String selection,String selectvalue,String schooltype){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where "+selection+"=? and schoollength in"+schooltype+" order by id";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1,selectvalue);
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("查询学校类型失败："+e.getMessage());
            return null;
        }
    }
    
    //查询对应学校详细信息
    public static Map checkSchoolDetail(String schoolname){
    	map=new HashedMap();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where schoolname=?";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1,schoolname);
            rs = st.executeQuery();
            while (rs.next()) {
            	map.put("region", rs.getString("region").trim());
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("address", rs.getString("address").trim());
            	map.put("schoollength", rs.getString("schoollength").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("tuition", rs.getString("tuition").trim());
            	map.put("enrollment", rs.getString("enrollment").trim());
            	map.put("otherschoolbus", rs.getString("otherschoolbus").trim());
            	map.put("otherschoolbed", rs.getString("otherschoolbed").trim());
            	map.put("otherschoolkey", rs.getString("otherschoolkey").trim());
            	map.put("schoollevel", rs.getString("schoollevel").trim());
            	map.put("schooltel", rs.getString("schooltel").trim());
            	map.put("schoolspecial", rs.getString("schoolspecial").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            }
            conn.close();
            return map;
        } catch (SQLException e) {  
            System.out.println("查询学校详细信息失败："+e.getMessage());
            return null;
        }
    }
    
    //查询首页对应学校详细信息
    public static Map checkUnableSchoolDetail(String schoolname){
    	map=new HashedMap();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where schoolname like '%"+schoolname+"%'";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            if (rs.next()) {
            	map.put("region", rs.getString("region").trim());
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("address", rs.getString("address").trim());
            	map.put("schoollength", rs.getString("schoollength").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("tuition", rs.getString("tuition").trim());
            	map.put("enrollment", rs.getString("enrollment").trim());
            	map.put("otherschoolbus", rs.getString("otherschoolbus").trim());
            	map.put("otherschoolbed", rs.getString("otherschoolbed").trim());
            	map.put("otherschoolkey", rs.getString("otherschoolkey").trim());
            	map.put("schoollevel", rs.getString("schoollevel").trim());
            	map.put("schooltel", rs.getString("schooltel").trim());
            	map.put("schoolspecial", rs.getString("schoolspecial").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            }
            conn.close();
            return map;
        } catch (SQLException e) {  
            System.out.println("查询学校详细信息失败："+e.getMessage());
            return null;
        }
    }
    
    //查询对应学校详细对口招生信息
    public static String checkSchoolEnrollment(String schoolname){
    	String result="";
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_enrollment where schoolname like '%"+schoolname+"%'";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	result=rs.getString("enrollment").trim();
            }
            conn.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询学校详细信息对口招生信息失败："+e.getMessage());
            return null;
        }
    }
    
  //查询对应学校详细对口跳转链接主页
    public static String checkSchoolUrl(String schoolname){
    	String result="";
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_url where schoolname like '%"+schoolname+"%'";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	result=rs.getString("schoolurl").trim();
            }
            conn.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询学校详细信息链接主页失败："+e.getMessage());
            return null;
        }
    }
    
    //学校详细信息列表分页---school_list
    public static List getPageSchool(String sq){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from edu_information_school where 1=1"+sq;
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("schoolregion", rs.getString("region").trim());
            	map.put("schooladdress", rs.getString("address").trim());
            	map.put("schoollength", rs.getString("schoollength").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("schooltel", rs.getString("schooltel").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("otherschoolbus", rs.getString("otherschoolbus").trim());
            	map.put("otherschoolbed", rs.getString("otherschoolbed").trim());
            	map.put("otherschoolkey", rs.getString("otherschoolkey").trim());
            	list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("学校详细信息列表分页失败："+e.getMessage());
            return null;
        }
    }
    
    //全局关键字查询学校
    public static List searchKeyInfo(String keyword){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "SELECT * FROM edu_information_school WHERE "
             +"CONCAT(region,schoolname,schoolgroup) "
             +"like '%"+keyword+"%' order by schoolname";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("schoolname", rs.getString("schoolname").trim());
            	map.put("schoolregion", rs.getString("region").trim());
            	map.put("schooladdress", rs.getString("address").trim());
            	map.put("schoollength", rs.getString("schoollength").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("schoolnature", rs.getString("schoolnature").trim());
            	map.put("schooltel", rs.getString("schooltel").trim());
            	map.put("schoolprofile", rs.getString("schoolprofile").trim());
            	map.put("schoolimgurl", rs.getString("schoolimgurl").trim());
            	map.put("schoolgroup", rs.getString("schoolgroup").trim());
            	map.put("otherschoolbus", rs.getString("otherschoolbus").trim());
            	map.put("otherschoolbed", rs.getString("otherschoolbed").trim());
            	map.put("otherschoolkey", rs.getString("otherschoolkey").trim());
            	list.add(map);
            }
            conn.close();
            return list;
        } catch (SQLException e) {  
            System.out.println("查询学校关键字失败："+e.getMessage());
            return null;
        }
    }
    
    //插入登记学生信息
    public static void saveStudent(String name,String mobile,String idcard,String email,String schoolname,String schoolarea,String schooltype,String campus){
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "insert into g_student(s_name,s_mobile,s_idcard,s_email,school_name,school_area,school_type,s_campus,s_time) "
            		+ "values('"+name+"','"+mobile+"','"+idcard+"','"+email+"','"+schoolname+"','"+schoolarea+"','"+schooltype+"','"+campus+"','"+getSystime()+"')";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            int count = st.executeUpdate(); 
            System.out.println("写入学生登记信息" + count + " 条数据"); 
            conn.close();   
        } catch (SQLException e) {  
            System.out.println("写入学生登记信息失败" + e.getMessage());  
        }  
    }
    
    public static void main(String[] args) {
    	int[] arr=new  int[]{8,2,1,0,3};
		int[] index=new int[]{2,0,3,2,4,0,1,3,2,3,3};
		String tel="";
		for (int i :index) {
			tel+=arr[i];
		}
		System.out.println(tel);
		System.out.println(checkSchoolType("幼儿园"));
	}
}
